<?php
// Copyright 2012 @Wondershare
// $Id: common.php 1 2012-8-20����3:44:20Z Administrator $
/**
 * 数据表操纵类
 */
class Common  {
	private $db  ;
	function __construct($arr = NULL){
		$this->db = $GLOBALS ['db'];
	}
	
	/**
	 * 根据cat_title以及站点类型得到category的相关信息，主要是为了得到cat_id
	 */
	public function getCategoryByCatTitle($cat_title , $cat_web)
	{
		$sql = 'SELECT * FROM  `category` WHERE  `cat_title` = \'' . $cat_title . '\' AND `is_delete`= 0  AND `cat_web` = '.$cat_web.' LIMIT 1';
		$category = $this->db->getOneRs($sql) ;
		return $category ;
	}
	
	/**
	 * 在表won_product中根据tag字段得到Products的相关信息 ,比如"热门推荐"、"人气产品"等
	 */
	public function getProductsByTag($tag , $web_type)
	{
		$sql = 'SELECT * FROM  `won_product` WHERE  `tag` = \'' . $tag . '\' AND `won_product_delete`= 0  AND `web_type` = ' . $web_type;
		$won_product = $this->db->getAll($sql) ;
		if($won_product!= null )
		{
			$products = array() ;
			foreach ($won_product as $product)
			{
				$sql = 'SELECT * FROM  `product` WHERE  `product_id` = ' . $product['won_product_id'] . ' AND `product_delete`= 0 LIMIT 1';
				$pro = $this->db->getOneRs($sql) ;
				if($pro != null)
				{
					$products[] = $pro ;
				}
			}
			return $products ;
		}
		return null ;
	}
	public function getWonProductsByTag($tag , $web_type)
	{
		$sql = 'SELECT * FROM  `won_product` WHERE  `tag` = \'' . $tag . '\' AND `won_product_delete`= 0 AND `web_type` = ' . $web_type;
		$won_product = $this->db->getAll($sql) ;
		return $won_product ;
	}
	
	/**
	 * 根据cat_id得到所有相关的product
	 * 首先由$cat_id在表won_product中查找所有相关的won_product
	 * 然后根据won_product的won_product_id字段在product中进行查找相关产品
	 */
	public function getProducsByCatID($cat_id)
	{
		$sql = 'SELECT * FROM  `won_product` WHERE  `cat_id` = '. $cat_id .' AND `won_product_delete`= 0 ';
		$won_product = $this->db->getAll($sql) ;
		if($won_product!= null )
		{
			$products = array() ;
			foreach ($won_product as $product)
			{
				$sql = 'SELECT * FROM  `product` WHERE  `product_id` = ' . $product['won_product_id'] . ' AND `product_delete`= 0 LIMIT 1';
				$pro = $this->db->getOneRs($sql) ;
				if($pro != null)
				{
					$products[] = $pro ;
				}
			}
			return $products ;
		}
		return null ;
	}
	
	/**
	 * 根据product_os得到所有的product列表 
	 */
	public function getProductsByOS($product_os)
	{
		$sql = 'SELECT * FROM  `product` WHERE  `product_os` = \'' . $product_os . '\'  AND `product_delete`= 0';
		$products = $this->db->getAll($sql) ;
		return $products ;
	}
	
	/**
	 * 根据product_id得到相关的product数据信息
	 */
	public function getProductByPID($product_id)
	{
		$sql = 'SELECT * FROM  `product` WHERE  `product_id` = ' . $product_id .' AND `product_delete`= 0 LIMIT 1';
		$product = $this->db->getOneRs($sql) ;
		return $product ;
	}
	
	/**
	 * 在category表中根据parent_id以及站点得到其所有的直接子类
	 */
	public function getChildCatsByPID($parent_id , $cat_web)
	{
		$sql = 'SELECT * FROM  `category` WHERE  `parent_id` = ' . $parent_id . ' AND `is_delete`= 0  AND  `cat_web` = ' . $cat_web;
		$products = $this->db->getAll($sql) ;
		return $products ;
	}
	
	/**
	 * 根据给定的$category得到其下的所有产品   -----包括非直接子类
	 */
	public function getAllProductsByCategory($category)
	{
		$cat_parent = explode(',', $category['parent_path']) ;
		$first_id = $category['cat_id'] ;
		$result_products = $this->getProducsByCatID($first_id) ;
		if($result_products == null )
			$result_products = array() ;
		
		$sql = 'SELECT * FROM  `category` WHERE  `parent_path` LIKE  \'%'.$first_id.'%\'  AND `is_delete`= 0';
		$categorys = $this->db->getAll($sql) ;
		$cat_child_arr[] = $first_id ;
		foreach ($categorys as $cat)
		{
			$cat_arr = explode(',', $cat['parent_path']) ;
			if(!array_search($first_id, $cat_arr))	//为了查找更准确
				continue ;
			$cat_arr[] = $cat['cat_id'];
			$cat_child_arr = array_merge($cat_arr , $cat_child_arr) ;
		}
		$cat_child_arr = array_unique($cat_child_arr) ;
		$cat_child_arr = array_diff($cat_child_arr, $cat_parent) ;
		
		foreach ($cat_child_arr as $cat_id )
		{
			$products = $this->getProducsByCatID($cat_id)  ;
			if($products != null )
			{
				$result_products = array_merge($products ,$result_products ) ;
			}
		}
		return $result_products ;
		/* echo count($result_products) ;
		echo '-------------------start----------------------------</hr>' ;
		echo '<pre>'.print_r($cat_child_arr , true ).'</pre>' ;
		echo '-------------------end----------------------------</hr>' ; */
	}
	/**
	 * 各个页面在展示产品时共同调用的
	 */
	public function commonShowProducts($category)
	{
		/* $sql = 'SELECT * FROM  `category` WHERE  `parent_id` = '.$category['cat_id'];
		$categorys = $this->db->getAll($sql) ;
		$products = array() ;
		foreach ($categorys as $category)
		{
			$products = $this->getAllProductsByCategory($category) ;
		} */
		$products = $this->getAllProductsByCategory($category) ;
		return $products ;
	}
	
	private $all_products = array() ;
	public function getAllProducts()
	{
		return $this->all_products ;
	}
	public function getAllChildProductsByCategory($category)		//绿色部分数据好像出现了重复，递归好像出现了问题。下面直接就用3层了
	{
		$products = array( ) ;
		$product  = $this->getProducsByCatID($category['cat_id'])  ;
		$cats = $this->getChildCatsByPID($category['cat_id'],  5) ;
		if(count($cats) > 0)
		{
			foreach ($cats as $category)
			{
// 				$this->getAllChildProductsByCategory($category) ;

				$product = $this->getProducsByCatID($category['cat_id'])  ;
				$cats = $this->getChildCatsByPID($category['cat_id'],  5) ;
				if(count($cats) > 0)
				{
					foreach ($cats as $category)
					{
						$product = $this->getProducsByCatID($category['cat_id'])  ;
						if($product != null)
						{
							$products = array_merge($product, $products);
						}
					}
				}
				else{
					if($product != null)
					{
						$products = array_merge($product, $products);
					}
				}
			}
		}else{
			if($product != null)
			{
				$products = array_merge($product, $products); 
// 				$this->all_products = array_merge($product, $this->all_products); 
			}
		}
		
		$this->all_products = $products ;
	}
	
	/**
	 * 根据$product_id得到该产品的相应价格 ， if($price['unique'])	就只有一种价格
	 */
	public function getPriceByPID($product_id)
	{
		$product_id = intval ( $product_id );
		$sql = "SELECT * FROM license WHERE `product_id`=" . $product_id;
		$result = $this->db->query ( $sql );
		while ( $item = $this->db->fetch_array( $result ) ) {
			if ($item ['license_id'] == 11) {
				if ($item ['sub_license_id'] == 0) {
					if ($item ['website_display']) {
						$price ['curr'] = $item ['price'];
					} else {
						$price ['old'] = $item ['price'];
						$price ['unique'] = true;
					}
				} else {
					if ($item ['website_display']) {
						$price ['curr'] = $item ['price'];
					}
				}
			}
		}
		if (empty ( $price ['curr'] )) {
			$price ['curr'] = $price ['old'];
			$price ['unique'] = true;
		}
		return $price;
	}
	
	/**
	 * 根据$product_id 得到对应的version表的相关信息----------正在销售的产品信息
	 */
	public function getVersionByPVersion($product_id)
	{
// 		$sql = 'SELECT * FROM  `version` WHERE  `version_name` = \'' .$product_version .'\' LIMIT 1';
		$sql = 'SELECT * FROM  `version` WHERE  `product_id` = ' .$product_id .' AND `is_sale` = ' . 1 .' LIMIT 1';
		$version = $this->db->getOneRs($sql) ;
		return $version ;
	}
	
	////////////////// 是won_product表中的ID呢？ 还是product表中的ID？？？	//////////////////////////////////////
	/**
	 * 在模版数据表Table_254中根据$product_id 得到对应的功能图标以及详细产品的URL等相关信息
	 */
	public function getIndexProductByPID($product_id)
	{
		$sql = 'SELECT * FROM  `Table_254` WHERE  `Field_22345` = '.$product_id.' LIMIT 1';
		$product = $this->db->getOneRs($sql) ;
		return $product ;
	}
	
	/**
	 * 在模版数据表Table_264中根据$product_id 得到对应的购买页信息---主要是得到URL
	 */
	public function getBuyDetailByPID($product_id)
	{
		$sql = 'SELECT * FROM `Table_264` WHERE `Field_22422`=' . $product_id . ' LIMIT 1' ;
		$buy_detail = $this->db->getOneRs($sql) ;
		return $buy_detail ;
	}
	
	/////////////////////////////////////// 2012 - 09 - 06 ////////////////////////////////////////////////////
	public function getWonProductsByCatID($cat_id)
	{
		$sql = 'SELECT * FROM  `won_product` WHERE  `cat_id` = '. $cat_id . ' AND `won_product_delete`= 0' ;
		$won_product = $this->db->getAll($sql) ;
		return $won_product ;
	}
	
	public function getAllWonProductsByCategory($category)
	{
		$cat_parent = explode(',', $category['parent_path']) ;
		$first_id = $category['cat_id'] ;
		$result_products = $this->getWonProductsByCatID($first_id) ;
		if($result_products == null )
			$result_products = array() ;
		
		$sql = 'SELECT * FROM  `category` WHERE  `parent_path` LIKE  \'%'.$first_id.'%\' AND `is_delete`= 0 ';
		$categorys = $this->db->getAll($sql) ;
		$cat_child_arr[] = $first_id ;
		foreach ($categorys as $cat)
		{
			$cat_arr = explode(',', $cat['parent_path']) ;
			if(!array_search($first_id, $cat_arr))	//为了查找更准确
				continue ;
			$cat_arr[] = $cat['cat_id'];
			$cat_child_arr = array_merge($cat_arr , $cat_child_arr) ;
		}
		$cat_child_arr = array_unique($cat_child_arr) ;
		$cat_child_arr = array_diff($cat_child_arr, $cat_parent) ;
		
		foreach ($cat_child_arr as $cat_id )
		{
			$products = $this->getWonProductsByCatID($cat_id)  ;
			if($products != null )
			{
				$result_products = array_merge($products ,$result_products ) ;
			}
		}
		return $result_products ;
	}
	
	
	/**
	 * 根据name和OS查找相应的产品
	 */
	public function getProductByNameAndOS($product_name , $os)
	{
		$sql = 'SELECT * FROM  `product` WHERE  `product_name` = \'' . $product_name .'\' AND `product_os` = \'' .$os .'\' AND `product_delete`= 0 LIMIT 1';
		$product = $this->db->getOneRs($sql) ;
		return $product ;
	}
	
	/**
	 * 根据D_ID得到购买页面的类型,mac or win
	 */
	public function getBuyCategory($D_ID)
	{
		$sql = 'SELECT * FROM `Table_263` WHERE `D_ID`=' . $D_ID . ' LIMIT 1' ;
		$buy_category = $this->db->getOneRs($sql) ;
		if($buy_category['Field_22423'] == null)
			$buy_category['Field_22423'] = 'Win' ;
		return $buy_category['Field_22423'] ;
	}
	
	/**
	 * 根据won_product-id查找对应的product_id
	 */
	public function getProductIDByWonProID($won_product_id)
	{
		$sql = 'SELECT `won_product_id` FROM  `won_product` WHERE  `id` = '.$won_product_id.' AND `won_product_delete`= 0 LIMIT 1';
		$product_id = $this->db->getOneRs($sql) ;
		return $product_id['won_product_id'];
	}

	//////////////////////////////////// 2012 - 09 - 07 ////////////////////////////////////////////////////////
	///////////////////////////////////	获取购买页的广告位	////////////////////////////////////////////////////
	public function getBuyADByPosition($position_type , $ad_web , $position_location , $position_name)
	{
// 		$sql = 'SELECT `position_id` FROM  `ad_position` WHERE  `position_type` = '.$position_type.' AND `position_location` = '.$position_location.' AND `is_delete`= 0  AND `ad_web` = '.$ad_web.' LIMIT 1';
		
		$sql = 'SELECT `position_id` FROM  `ad_position` WHERE  `position_type` = '.$position_type.' AND `position_location` = '.$position_location.' AND `is_delete`= 0  AND `ad_web` = '.$ad_web.' AND `position_name` = \''.$position_name.'\' LIMIT 1';
		$position = $this->db->getOneRs($sql) ;
		if($position != null)
		{
			$position_id = $position['position_id'];
			$sql = 'SELECT `ad_id` FROM  `ad_delivery` WHERE  `position_id` = '.$position_id.' AND  `ad_web` = '.$ad_web.' AND  `position_type` = '.$position_type.' LIMIT 1';
			$delivery = $this->db->getOneRs($sql) ;
			if($delivery != null)
			{
				$ad_id = $delivery['ad_id'] ;
				$sql = 'SELECT * FROM  `ad` WHERE  `ad_id` = '.$ad_id.' AND  `ad_web` = '.$ad_web.' AND  `is_delete` = 0 LIMIT 1'; 
				$ad = $this->db->getOneRs($sql) ;
				if($ad != null)
					return $ad ;
			}
		}
		return null ;
	}

}